package com.tansun.tandata.utils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.alibaba.fastjson.JSON;
import com.tansun.tandata.enums.DictionaryTypeEnum;
import com.tansun.tandata.enums.IndicatorsTypeEnum;
import org.apache.commons.lang.StringUtils;

/**
 * 指标sql生成工具类
 *
 * @author gaoyufa
 *
 */
public class IndicatorsSqlUtil {
    // 源表信息集合
    public Map<String, Object> sourceTableMap = new HashMap<String, Object>();
    // 目标表信息集合
    public Map<String, Object> sqlMap = new HashMap<String, Object>();
    // sql组件信息集合
    public Map<String, Object> targetTableMap = new HashMap<String, Object>();
    // 以sql组件的id作为value，以目标表的id作为key
    public Map<String, String> fromMap = new HashMap<String, String>();
    // 指标实体集合
    private List<Map<String, String>> indexList = new ArrayList<Map<String,String>>();
    // 衍生公式sql片段
    private String operationSql = "";
    // 源表id与源表别名的映射
    private Map<String, String> aliasNameMap = new HashMap<String, String>();

    /**
     * 初始化赋值
     *
     * @param jsonStr
     *            需要解析json串
     */
    @SuppressWarnings("unchecked")
    public IndicatorsSqlUtil(String jsonStr, String type) {
        if (IndicatorsTypeEnum.BASIC_INDEX.getVaule().equals(type)){
            basicIndexInit(jsonStr);
        }
        if (IndicatorsTypeEnum.DERIVATIVE_INDEX.getVaule().equals(type)){
            derivativeIndexInit(jsonStr);
        }
    }

    /**
     * 基础指标初始化
     *
     * @param jsonStr
     */
    @SuppressWarnings("unchecked")
    private void basicIndexInit(String jsonStr){
        Map<String, Object> map = JSON.parseObject(jsonStr);
        List<Map<String, Object>> nodeList = (List<Map<String, Object>>) map.get("nodeList");
        // 拿取各个组件的信息，放入对应集合里面
        for (Map<String, Object> node : nodeList) {
            if (node.get("type").equals("TABLE")) {
                sourceTableMap.put((String) node.get("id"), node.get("entity"));
            }
            if (node.get("type").equals("SQL")) {
                sqlMap.put((String) node.get("id"), node.get("entity"));
            }
            if (node.get("type").equals("TARGET")) {
                targetTableMap.put((String) node.get("id"), node.get("entity"));
            }
        }
        List<Map<String, Object>> lineList = (List<Map<String, Object>>) map.get("lineList");
        for (String key : sqlMap.keySet()) {
            for (Map<String, Object> line : lineList) {
                if (line.get("from").equals(key)) {
                    fromMap.put(key, (String) line.get("to"));
                }
            }
        }
        for (String key : sourceTableMap.keySet()) {
            Map<String,Object> sourceTable = (Map<String, Object>) sourceTableMap.get(key);
            String tableAliasName = (String) sourceTable.get("tableAliasName");
            if (StringUtils.isEmpty(tableAliasName)) {
                throw new RuntimeException("请在表连接关系界面填写表别名！");
            }
            aliasNameMap.put((String)sourceTable.get("tableId"), tableAliasName);
        }
    }

    /**
     * 衍生指标初始化
     *
     * @param jsonStr
     */
    @SuppressWarnings("unchecked")
    private void derivativeIndexInit(String jsonStr){
        Map<String, Object> jsonMap = JSON.parseObject(jsonStr);
        List<Map<String, Object>> colInfo = (List<Map<String, Object>>) jsonMap.get("calInfo");
        for (Map<String, Object> col : colInfo) {
            if(col.get("content") != null){
                indexList.add((Map<String, String>)col.get("content"));
            }
        }
        operationSql = (String) jsonMap.get("mappingSql");
    }

    /**
     * 获取目标表map集合
     *
     * @return
     */
    public Map<String, Object> getTargetTableMap() {
        return targetTableMap;
    }

    /**
     * 获取源表map集合
     *
     * @return
     */
    public Map<String, Object> getSourceTableMap() {
        return sourceTableMap;
    }

    /**
     * 获取指标实体集合
     *
     * @return
     */
    public List<Map<String, String>> getIndexList() {
        return indexList;
    }

    /**
     * 返回基础指标sql集合
     *
     * @param dimType      维度值
     * @param descColName  目标表字段名
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<String> basicIndicatorsSqlList(String dimType,String descColName) {
        List<String> sqlList = new ArrayList<String>();
        for (String key : sqlMap.keySet()) {
            Map<String, Object> targetTable = (Map<String, Object>) targetTableMap.get(fromMap.get(key));
            Map<String, Object> sqlEntity = (Map<String, Object>) sqlMap.get(key);
            sqlList.add(basicIndicatorsSql(targetTable, sqlEntity,dimType,descColName));
        }
        return sqlList;
    }

    /**
     * 基础指标sql生成
     *
     * @param targetTable  目标表实体
     * @param sqlEntity    sql组件实体
     * @param dimType      维度值
     * @param descColName  目标表字段名
     * @return
     */
    @SuppressWarnings("unchecked")
    public String basicIndicatorsSql(Map<String, Object> targetTable, Map<String, Object> sqlEntity,String dimType,String descColName) {
        StringBuffer sql = new StringBuffer("/* INSERT INTO " + targetTable.get("tableName") + " (id,data_dt,dim_type,create_time,");
        String filedStr = "";
        String insertFiled = "";
        // 获取目标表的映射字段集合
        List<Map<String, String>> fileds = (List<Map<String, String>>) targetTable.get("fileds");
        for (Map<String, String> filed : fileds) {
            if(StringUtils.isEmpty(filed.get("tableAliasName")) || StringUtils.isEmpty(filed.get("src"))){
               throw new RuntimeException("目标源表的所有字段都是必填的！");
            }
            // 遍历映射字段，组成查询的字段
            filedStr += aliasNameMap.get(filed.get("tableAliasName")) + "." + filed.get("src") + " AS " + filed.get("code").toLowerCase() + ",";
            // 遍历目标表字段，组成目标表插入字段串
            insertFiled += filed.get("code") + ",";
        }
        // 拼接插入的目标表字段
        sql.append(insertFiled.substring(0, insertFiled.length() - 1) + ") ");
        // 拼接查询结果的字段
        sql.append("*/ SELECT UUID() AS id,DATE_FORMAT(NOW(), '%Y-%m-%d') AS data_dt,'" + dimType + "' AS dim_type,NOW() AS create_time," + filedStr.substring(0, filedStr.length() - 1));
        // 拼接查询主表
        sql.append(" FROM " + sqlEntity.get("mainTable") + " " + sqlEntity.get("mainTableAliasName") + " ");
        // 拼接联表语句+where语句+分组语句
        sql.append(getJoinString(sqlEntity)).append(getWhereString(sqlEntity)).append(getGroupString(sqlEntity));
        // 拼接更新语句
        sql.append("/* ON DUPLICATE KEY UPDATE " + descColName + " = VALUES(" + descColName + ") */");
        return sql.toString();
    }

    /**
     * 获取where拼接的条件字符串
     *
     * @param sqlEntity
     * @return
     */
    @SuppressWarnings("unchecked")
    private String getWhereString(Map<String, Object> sqlEntity) {
        List<Map<String, String>> wheres = (List<Map<String, String>>) sqlEntity.get("where");
        if (wheres != null && wheres.size() != 0) {
            String whereStr = "";
            for (Map<String, String> where : wheres) {
                String filedName = where.get("filedName"), whereSt = where.get("where") , value = where.get("value"),tableAliasName = aliasNameMap.get(where.get("tableAliasName"));
                if (StringUtil.isEmpty(filedName) || StringUtil.isEmpty(whereSt) || StringUtil.isEmpty(value) || StringUtil.isEmpty(tableAliasName)) {
                    if (StringUtil.isEmpty(filedName) && StringUtil.isEmpty(whereSt) && StringUtil.isEmpty(value) && StringUtil.isEmpty(tableAliasName)) {
                        continue;
                    }
                    throw new RuntimeException("条件筛选条件请填写完整！");
                }
                // 条件语句拼接
                whereStr += tableAliasName + "." + filedName + whereSt + "'" + value + "'" + " AND ";
            }
            if (!StringUtils.isEmpty(whereStr)) {
                // 去掉最后一个AND
                return "WHERE " + whereStr.substring(0, whereStr.length() - 4);
            }
        }
        return "";
    }

    /**
     * 获取联表查询拼接语句
     *
     * @param sqlEntity
     * @return
     */
    @SuppressWarnings("unchecked")
    private String getJoinString(Map<String, Object> sqlEntity) {
        if (StringUtils.isEmpty((String)sqlEntity.get("type"))) {
            throw new RuntimeException("源表之间的连接关系没有选择！");
        }
        String joinStr = sqlEntity.get("type") + " " + sqlEntity.get("tableName") + " "
                + sqlEntity.get("tableAliasName") + " ON ";
        // 获取联表查询条件数组
        List<Map<String, String>> ons = (List<Map<String, String>>) sqlEntity.get("on");
        String onStr = "";
        if (ons == null || ons.size() == 0) {
            throw new RuntimeException("基础指标sql表关联条件不能为空！");
        }
        for (Map<String, String> on : ons) {
            String filedName1 = on.get("filedName1"),filedName2 = on.get("filedName2"), where = on.get("where");
            if (StringUtil.isEmpty(filedName2) || StringUtil.isEmpty(filedName1) || StringUtil.isEmpty(where)) {
                throw new RuntimeException("连接关系的筛选条件请填写完整！");
            }
            // 关联查询的条件语句拼接
            onStr += on.get("tableAliasName1") + "." + filedName1 + where + on.get("tableAliasName2") + "." + filedName2 + " AND ";
        }
        // 去掉最后一个AND
        return joinStr + onStr.substring(0, onStr.length() - 4);
    }

    /**
     * 获取分组筛选拼接语句
     *
     * @param sqlEntity
     * @return
     */
    @SuppressWarnings("unchecked")
    private String getGroupString(Map<String, Object> sqlEntity) {
        List<Map<String, String>> groupBies = (List<Map<String, String>>) sqlEntity.get("groupBy");
        if (groupBies != null && groupBies.size() != 0) {
            String groupStr = "";
            for (Map<String, String> groupBy : groupBies) {
                String filedName = groupBy.get("filedName"),tableAliasName = aliasNameMap.get(groupBy.get("tableAliasName"));
                if (StringUtil.isEmpty(filedName) || StringUtil.isEmpty(tableAliasName)) {
                    if (StringUtil.isEmpty(filedName) && StringUtil.isEmpty(tableAliasName)) {
                        continue;
                    }
                    throw new RuntimeException("分组筛选条件请填写完整！");
                }
                // 分组条件语句拼接
                groupStr += tableAliasName+ "." + filedName + ",";
            }
            if (!StringUtils.isEmpty(groupStr)) {
                // 去掉最后一个,
                return "GROUP BY " + groupStr.substring(0, groupStr.length() - 1);
            }
        }
        return "";
    }

    /**
     * 衍生指标sql生成
     *
     * @param resultField   目标表结果字段名
     * @return
     */
    public String derivativeIndexSql(String resultField){
        // 目标表名
        String target = DictionaryTypeEnum.TARGET_TABLE_NAME.getVaule();
        StringBuffer sql = new StringBuffer("UPDATE " + target + " t INNER JOIN (SELECT ");
        // 如果log或者ln做转译
        if(operationSql.contains("log(")){
            operationSql = operationSql.replace("log(", "log(10,");
        }
        if(operationSql.contains("In(")){
            operationSql = operationSql.replace("In(", "log(exp(1),");
        }
        sql.append(operationSql + " AS " + resultField + ",now() AS modify_time,id FROM " + target);
        sql.append(" WHERE DATA_DT = DATE_FORMAT(NOW(), '%Y-%m-%d') ) b ON t.id= b.id SET t." + resultField + " = b." + resultField);
        sql.append(",t.modify_time = b.modify_time");
        return sql.toString();
    }

    public static void main(String[] args) {
        ExecutorService service = Executors.newCachedThreadPool();
        service.execute(() -> {
            String jsonStr = "{\"nodeList\":[{\"id\":\"6j2g6ywyh7\",\"name\":\"对公活期存款\",\"type\":\"TABLE\",\"menuId\":\"Metadata566d75953204478081293fedbf23dce3\",\"left\":\"207px\",\"top\":\"61px\",\"ico\":\"el-icon-time\",\"show\":true,\"code\":\"t_idx_src_acinfo\",\"entity\":{\"tableType\":\"1\",\"tableName\":\"t_idx_src_acinfo\",\"tableAliasName\":\"a1\",\"tableId\":\"Metadata566d75953204478081293fedbf23dce3\"},\"data\":[{\"code\":\"DATA_BSN_DT\",\"name\":\"数据业务日期\",\"menuId\":\"Metadata1e19e5645d98494c83fd441d7d8a64f3\",\"type\":\"DATE()\"},{\"code\":\"CST_ACCNO\",\"name\":\"客户账号\",\"menuId\":\"Metadata78a8ab6896314c139df2424f057d5e2f\",\"type\":\"VARCHAR(32)\"},{\"code\":\"DPBKINNO\",\"name\":\"开户机构编号\",\"menuId\":\"Metadata7c5a19d1f5da41c8833a6d5487fdba4c\",\"type\":\"VARCHAR(9)\"},{\"code\":\"CCYCD\",\"name\":\"币种代码\",\"menuId\":\"Metadata92468cecb5a84d93a721d8cf47158cf2\",\"type\":\"VARCHAR(3)\"},{\"code\":\"AVL_BAL\",\"name\":\"可用余额\",\"menuId\":\"Metadataac85a53012354f3cb0d457fec6d7d537\",\"type\":\"DECIMAL()\"},{\"code\":\"DEP_OD_AMT\",\"name\":\"存款透支金额\",\"menuId\":\"Metadatac2a9939682ae450795d1566b9de065fe\",\"type\":\"DECIMAL()\"},{\"code\":\"DEP_ACBA\",\"name\":\"存款账户余额\",\"menuId\":\"Metadatad3a5971fdd49445383a74c11386585b6\",\"type\":\"INT()\"}]},{\"id\":\"s1u6sam5ho\",\"name\":\"交易明细表\",\"type\":\"TABLE\",\"menuId\":\"Metadata7123d6b70285466f83bb849ce0dadb52\",\"left\":\"544px\",\"top\":\"81px\",\"ico\":\"el-icon-time\",\"show\":true,\"code\":\"t_idx_src_txn_dtl\",\"entity\":{\"tableType\":\"1\",\"tableName\":\"t_idx_src_txn_dtl\",\"tableAliasName\":\"b1\",\"tableId\":\"Metadata7123d6b70285466f83bb849ce0dadb52\"},\"data\":[{\"code\":\"TXN_INSID\",\"name\":\"交易机构编号\",\"menuId\":\"Metadata1a1a07265ba8414fbb8c1bfd86e5496a\",\"type\":\"VARCHAR(11)\"},{\"code\":\"CCYCD\",\"name\":\"币种代码\",\"menuId\":\"Metadata67bb6fce480c49dbbcb6989981df4639\",\"type\":\"VARCHAR(4)\"},{\"code\":\"TXN_TYPE\",\"name\":\"交易类型\",\"menuId\":\"Metadata6aef7e2b82da48dbb6c25b68fbf983c7\",\"type\":\"VARCHAR(6)\"},{\"code\":\"OVRLSTTN_EV_TRCK_NO\",\"name\":\"全局事件跟踪号\",\"menuId\":\"Metadata82e68bdcc1c54028a4ee751ab4dfbf8f\",\"type\":\"VARCHAR(25)\"},{\"code\":\"AC_ID\",\"name\":\"账户编号\",\"menuId\":\"Metadataa853a3f49a654acbae97bc8777d4b2fb\",\"type\":\"VARCHAR(32)\"},{\"code\":\"CHNL_NO\",\"name\":\"交易发起渠道编号\",\"menuId\":\"Metadataae44ad18457d4726a29fa8003b5f20ee\",\"type\":\"VARCHAR(23)\"},{\"code\":\"TXN_AMT\",\"name\":\"交易金额\",\"menuId\":\"Metadataecccfac240254444b6c72bd3a302cae9\",\"type\":\"DECIMAL()\"},{\"code\":\"DATA_BSN_DT\",\"name\":\"数据业务日期\",\"menuId\":\"Metadataf6645c6232a2468cbfe795e69807f7c5\",\"type\":\"DATE()\"}]},{\"id\":\"qtw035bzdx\",\"name\":\"SQL\",\"type\":\"SQL\",\"menuId\":\"3\",\"left\":\"434px\",\"top\":\"179px\",\"ico\":\"el-icon-time\",\"show\":true,\"entity\":{\"mainTable\":\"t_idx_src_acinfo\",\"mainTableAliasName\":\"a1\",\"type\":\"left join\",\"on\":[{\"tableAliasName1\":\"a1\",\"filedName1\":\"DPBKINNO\",\"where\":\"=\",\"tableAliasName2\":\"b1\",\"filedName2\":\"TXN_INSID\"}],\"tableName\":\"t_idx_src_txn_dtl\",\"tableAliasName\":\"b1\",\"where\":[{\"tableAliasName\":\"Metadata566d75953204478081293fedbf23dce3\",\"filedName\":\"AVL_BAL\",\"where\":\">\",\"value\":\"0\"},{\"tableAliasName\":\"\",\"filedName\":\"\",\"where\":\"\",\"value\":\"\"}],\"groupBy\":[{\"tableAliasName\":\"Metadata7123d6b70285466f83bb849ce0dadb52\",\"filedName\":\"TXN_AMT\"}]}},{\"id\":\"v1zzbixu2b\",\"name\":\"指标结果表\",\"type\":\"TARGET\",\"menuId\":\"Metadataee436d07112e49f6b8c589529c120\",\"left\":\"482px\",\"top\":\"274px\",\"ico\":\"el-icon-time\",\"show\":true,\"code\":\"t_idx_result_wide\",\"entity\":{\"tableName\":\"t_idx_result_wide\",\"tableAliasName\":\"\",\"tableId\":\"Metadataee436d07112e49f6b8c589529c120\",\"tableType\":\"2\",\"fileds\":[{\"code\":\"NUM_COL122\",\"name\":\"数字型字段122\",\"menuId\":\"Metadata97c7f65800ca49ceaa4a8851c136fb1135\",\"type\":\"VARCHAR(30)\",\"tableAliasName\":\"Metadata566d75953204478081293fedbf23dce3\",\"src\":\"DEP_ACBA\",\"aliasName\":\"NUM_COL122\"},{\"code\":\"ORG_CODE\",\"name\":\"机构编号\",\"menuId\":\"Metadata97c7f65800ca49ceaa4a8851c7fb1006\",\"type\":\"VARCHAR(30)\",\"aliasName\":\"ORG_CODE\",\"tableAliasName\":\"Metadata7123d6b70285466f83bb849ce0dadb52\",\"src\":\"TXN_INSID\"},{\"code\":\"org_name\",\"name\":\"机构名称\",\"menuId\":\"Metadata023bb8d24cc946eda54b57c740bd3c53\",\"type\":\"VARCHAR(255)\",\"aliasName\":\"org_name\",\"tableAliasName\":\"Metadata566d75953204478081293fedbf23dce3\",\"src\":\"DEP_ACBA\"}]},\"data\":[{\"code\":\"NUM_COL122\",\"name\":\"数字型字段122\",\"menuId\":\"Metadata97c7f65800ca49ceaa4a8851c136fb1135\",\"type\":\"VARCHAR(30)\",\"tableAliasName\":\"Metadata566d75953204478081293fedbf23dce3\",\"src\":\"DEP_ACBA\",\"aliasName\":\"NUM_COL122\"},{\"code\":\"ORG_CODE\",\"name\":\"机构编号\",\"menuId\":\"Metadata97c7f65800ca49ceaa4a8851c7fb1006\",\"type\":\"VARCHAR(30)\",\"aliasName\":\"ORG_CODE\",\"tableAliasName\":\"Metadata7123d6b70285466f83bb849ce0dadb52\",\"src\":\"TXN_INSID\"},{\"code\":\"org_name\",\"name\":\"机构名称\",\"menuId\":\"Metadata023bb8d24cc946eda54b57c740bd3c53\",\"type\":\"VARCHAR(255)\",\"aliasName\":\"org_name\",\"tableAliasName\":\"Metadata566d75953204478081293fedbf23dce3\",\"src\":\"DEP_ACBA\"}]}],\"lineList\":[{\"from\":\"6j2g6ywyh7\",\"to\":\"qtw035bzdx\"},{\"from\":\"s1u6sam5ho\",\"to\":\"qtw035bzdx\"},{\"from\":\"qtw035bzdx\",\"to\":\"v1zzbixu2b\"}],\"moveTableLineList\":[{\"from\":\"6j2g6ywyh7\",\"to\":\"s1u6sam5ho\"}]}";
            IndicatorsSqlUtil util = new IndicatorsSqlUtil(jsonStr,IndicatorsTypeEnum.BASIC_INDEX.getVaule());
            System.out.println(util);
            System.out.println(util.basicIndicatorsSqlList("dimension_org","NUM_COL17"));
        });
        service.shutdown();
    }

}